﻿using Model;
using System;
using System.Collections.Generic;
using System.Data;
using Utility.Helper;

namespace BLL
{
    public class QS_PLAN_DETAIL1_BLL : BaseBLL
    {
        #region 基础

        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public bool Insert(QS_PLAN_DETAIL1 entity, string userid = "null")
        {
            var seq = GetSequence("SEQ_N_ID1");
            entity.N_ID1 = seq;
            var sql = @"insert into qs_plan_detail1
  (n_id1, n_pro_id, n_type, n_planapp_id, n_num, c_proname, n_dosage, n_price, n_amount, c_bank, c_remark,n_userid)
values
  ({0}, {1}, {2}, {3}, {4}, '{5}', {6}, {7}, {8}, '{9}', '{10}',{11})";
            sql = string.Format(sql,
                entity.N_ID1,
                entity.N_PRO_ID,
                entity.N_TYPE,
                entity.N_PLANAPP_ID,
                entity.N_NUM == null ? 0 : entity.N_NUM,
                entity.C_PRONAME == null ? "" : entity.C_PRONAME,
                entity.N_DOSAGE == null ? 0 : entity.N_DOSAGE,
                entity.N_PRICE == null ? 0 : entity.N_PRICE,
                entity.N_AMOUNT == null ? 0 : entity.N_AMOUNT,
                entity.C_BANK == null ? "" : entity.C_BANK,
                entity.C_REMARK == null ? "" : entity.C_REMARK,
                userid);
            if (NoQuery(sql) == 0)
            {
                return false;
            }
            return true;
        }

        /// <summary>
        ///
        /// </summary>
        /// <param name="applyId"></param>
        /// <param name="state"></param>
        /// <returns></returns>
        public bool DeleteByApplyId(decimal applyId, decimal proId, int n_type, string userid = null)
        {
            var sql = "delete qs_plan_detail1 where n_planapp_id = " + applyId + " AND N_PRO_ID=" + proId + @" AND n_type=" + n_type;

            if (!string.IsNullOrEmpty(userid))
            {
                sql += " AND N_USERID = " + userid;
            }

            if (NoQuery(sql) == 0)
            {
                return false;
            }
            return true;
        }

        #endregion 基础

        #region 月资金相关

        public List<QS_PLAN_DETAIL1> FindRangeMonth1(int applyId, int proId, int type, string userId = "null")
        {
            //var sql = @"select *
            //     from qs_plan_detail1
            //    WHERE n_planapp_id=" + applyId;
            //sql += " AND n_type=" + type + @" AND n_Pro_Id=" + proId;
            //sql += " ORDER BY n_id1";

            if (userId == "null")
            {
                userId = base.User.USER_ID.ToString();
            }

            string sql = null;

            //查询周(汇总或成员)
            if (type == 3 || type == 5)
            {
                sql = @"
  SELECT
    T2.*,
    CASE
      WHEN T2.N_AMOUNT IS NULL THEN '0%'
      WHEN T2.N_AMOUNT =0 THEN '0%'
      WHEN T2.N_USEAMOUNT IS NULL THEN '0%'
      WHEN T2.N_USEAMOUNT =0 THEN '0%'
      ELSE
         TO_CHAR(ROUND(T2.N_USEAMOUNT/T2.N_AMOUNT*100,6))||'%'
    END AS N_GOL
  FROM QS_PLANWEEKAPPLY T1
  INNER JOIN QS_PLAN_DETAIL1 T2 ON T1.N_WEEKAPP_ID = T2.N_PLANAPP_ID AND T2.N_TYPE = :N_TYPE
  WHERE
        T1.N_WEEKAPP_ID = :APP_ID";

                //若为周成员则添加一个条件
                if (type == 5)
                {
                    sql += @" AND T2.N_USERID = :N_USERID";
                }
            }

            //查询月(汇总或成员)
            if (type == 2 || type == 4)
            {
                sql = @"SELECT
  T1.*,
  T2.N_GOL
FROM
  (SELECT
    T2.*
  FROM QS_PLANMONTHAPPLY T1
  INNER JOIN QS_PLAN_DETAIL1 T2 ON T1.N_MONTHAPP_ID = T2.N_PLANAPP_ID AND T2.N_TYPE = :N_TYPE";

                //若为月成员则添加一个条件
                if (type == 4)
                {
                    sql += @" AND T2.N_USERID = :N_USERID";
                }

                sql += @" WHERE
    T1.N_MONTHAPP_ID = :APP_ID
  ) T1
FULL JOIN (
SELECT
  T1.*,
  CASE
      WHEN T1.N_AMOUNT IS NULL THEN '0%'
      WHEN T1.N_AMOUNT = 0 THEN '0%'
      WHEN T1.N_USEAMOUNT IS NULL THEN '0%'
      WHEN T1.N_USEAMOUNT = 0 THEN '0%'
      ELSE TO_CHAR(ROUND(T1.N_USEAMOUNT/T1.N_AMOUNT*100,2))||'%'
   END AS N_GOL
FROM
(
  SELECT
    T2.C_PRONAME,
    SUM(
      CASE
        WHEN T2.N_DOSAGE IS NULL THEN 0
        ELSE T2.N_DOSAGE
      END) AS N_DOSAGE,
    SUM(
      CASE
        WHEN T2.N_PRICE IS NULL THEN 0
        ELSE T2.N_PRICE
      END) AS N_PRICE,
    SUM(
      CASE
        WHEN T2.N_AMOUNT IS NULL THEN 0
        ELSE T2.N_AMOUNT
      END) AS N_AMOUNT,
    SUM(
      CASE
        WHEN T2.N_USEAMOUNT IS NULL THEN 0
        ELSE T2.N_USEAMOUNT
      END) AS N_USEAMOUNT
  FROM QS_PLANWEEKAPPLY T1
  INNER JOIN QS_PLAN_DETAIL1 T2 ON T1.N_WEEKAPP_ID = T2.N_PLANAPP_ID AND T2.N_TYPE = 3
  INNER JOIN QS_PLANMONTHAPPLY T3 ON T1.N_MONTHAPP_ID = T3.N_MONTHAPP_ID
  WHERE
    T1.N_BUSINESS_STATE = 2 AND
    T1.N_MONTHAPP_ID = :APP_ID
  GROUP BY
    T2.C_PRONAME) T1) T2 ON T1.C_PRONAME = T2.C_PRONAME";
            }

            List<Tuple<string, string, string>> parms = new List<Tuple<string, string, string>>();

            if (type == 4 || type == 5)
            {
                parms.Add(new Tuple<string, string, string>(":N_USERID", userId, "int"));
            }

            parms.Add(new Tuple<string, string, string>(":APP_ID", applyId.ToString(), "int"));
            parms.Add(new Tuple<string, string, string>(":N_TYPE", type.ToString(), "int"));

            //return dt;
            var dt = Query(sql, parms);
            var entitys = new List<QS_PLAN_DETAIL1>();
            foreach (System.Data.DataRow row in dt.Rows)
            {
                var entity = new QS_PLAN_DETAIL1();

                entity.N_ID1 = decimal.Parse(row["N_ID1"].ToString());
                entity.N_PRO_ID = decimal.Parse(row["N_PRO_ID"].ToString());
                entity.N_TYPE = decimal.Parse(row["N_TYPE"].ToString());

                entity.N_PLANAPP_ID = decimal.Parse(row["N_PLANAPP_ID"].ToString());
                entity.N_NUM = row["N_NUM"] == null ? 0 : decimal.Parse(row["N_NUM"].ToString());
                entity.C_PRONAME = row["C_PRONAME"].ToString();

                entity.N_DOSAGE = row["N_DOSAGE"] == null ? 0 : decimal.Parse(row["N_DOSAGE"].ToString());
                entity.N_PRICE = row["N_PRICE"] == null ? 0 : decimal.Parse(row["N_PRICE"].ToString());

                entity.N_AMOUNT = row["N_AMOUNT"] == null ? 0 : decimal.Parse(row["N_AMOUNT"].ToString());
                entity.C_BANK = row["C_BANK"].ToString();

                entity.C_REMARK = row["C_REMARK"].ToString();

                entity.N_AMT = row["N_USEAMOUNT"] == System.DBNull.Value ? 0 : decimal.Parse(row["N_USEAMOUNT"].ToString());
                entity.C_AMTPRO = row["N_GOL"].ToString();

                entitys.Add(entity);
            }
            return entitys;
        }

        public void UpdateOrCreateRangeMonth1(int applyId, int proId, int type, List<QS_PLAN_DETAIL1> data)
        {
            //先清空再添加
            if (type == 4 || type == 5)
            {
                DeleteByApplyId(applyId, proId, type, base.User.USER_ID.ToString());
            }
            else
            {
                DeleteByApplyId(applyId, proId, type);
            }

            int count = 0;
            foreach (var item in data)
            {
                var entity = new QS_PLAN_DETAIL1();
                entity.N_NUM = item.N_NUM;
                entity.C_PRONAME = item.C_PRONAME;
                entity.N_DOSAGE = item.N_DOSAGE;
                entity.N_PRICE = item.N_PRICE;
                entity.N_AMOUNT = item.N_AMOUNT;
                entity.C_BANK = item.C_BANK.ToString();
                entity.C_REMARK = item.C_REMARK.ToString();
                entity.N_PRO_ID = proId;
                entity.N_PLANAPP_ID = applyId;
                entity.N_TYPE = item.N_TYPE;
                if (Insert(entity, base.User.USER_ID.ToString()))
                {
                    count++;
                }
            }

            var sql = @"update qs_planmonth_detail t
   set t.n_amount =(select nvl( sum(t.N_AMOUNT),0)
  from qs_plan_detail1 t
 where t.n_type = " + type + @"
   and t.n_pro_id =" + proId + @"
   and t.n_planapp_id = " + applyId + @"
 )
 where t.n_monthapp_id = " + applyId + @"
   and t.n_proid =" + proId;

            if (type == 3)
            {
                //周计划明细
                sql = @"update qs_planweek_detail t
   set t.n_amount =(select nvl( sum(t.N_AMOUNT),0)
  from qs_plan_detail1 t
 where t.n_type = " + type + @"
   and t.n_pro_id =" + proId + @"
   and t.n_planapp_id = " + applyId + @"
 )
 where t.N_WEEKAPP_ID = " + applyId + @"
   and t.n_proid =" + proId;
            }

            if (type == 4)
            {
                //月计划部门详情
                sql = @"update QS_PLANMONTH_DEPARTMENT t
   set t.N_MONEY =(select nvl( sum(t.N_AMOUNT),0)
  from qs_plan_detail1 t
 where t.n_type = " + type + @"
   and t.n_pro_id =" + proId + @"
   and t.n_planapp_id = " + applyId + @"
   and t.n_userid = " + base.User.USER_ID + @"
 )
 where t.N_APPLY_ID = " + applyId + @"
   and t.N_PROJECT_ID =" + proId + @"
   and t.N_USER_ID =" + base.User.USER_ID;
            }

            if (type == 5)
            {
                //周计划部门详情
                sql = @"update QS_PLANWEEK_DEPARTMENT t
   set t.N_MONEY =(select nvl( sum(t.N_AMOUNT),0)
  from qs_plan_detail1 t
 where t.n_type = " + type + @"
   and t.n_pro_id =" + proId + @"
   and t.n_planapp_id = " + applyId + @"
   and t.n_userid = " + base.User.USER_ID + @"
 )
 where t.N_APPLY_ID = " + applyId + @"
   and t.N_PROJECT_ID =" + proId + @"
   and t.N_USER_ID =" + base.User.USER_ID;
            }

            var dt = NoQuery(sql);
        }

        /// <summary>
        /// 将所有部门详情GROUP合并并添加到汇总里面(月)
        /// </summary>
        public void GetMonthCollect(int applyId, int proId)
        {
            string sql = @"select N_PRO_ID,
       N_TYPE,
       N_PLANAPP_ID,
       C_PRONAME,
       round(sum(N_DOSAGE),6) N_DOSAGE,
       round(avg(N_PRICE),6) N_PRICE,
       round(sum(N_AMOUNT),6) N_AMOUNT
  from qs_plan_detail1
 WHERE n_planapp_id = " + applyId + @"
   AND n_type = 4
   AND n_Pro_Id = " + proId + @"
 GROUP BY N_PRO_ID, N_TYPE, N_PLANAPP_ID, C_PRONAME";

            var dt = Query(sql);
            var entitys = new List<QS_PLAN_DETAIL1>();
            decimal num = 0;
            foreach (System.Data.DataRow row in dt.Rows)
            {
                num++;

                var entity = new QS_PLAN_DETAIL1();

                entity.N_PRO_ID = decimal.Parse(row["N_PRO_ID"].ToString());
                entity.N_TYPE = 2;

                entity.N_PLANAPP_ID = decimal.Parse(row["N_PLANAPP_ID"].ToString());
                entity.N_NUM = num;
                entity.C_PRONAME = row["C_PRONAME"].ToString();

                entity.N_DOSAGE = row["N_DOSAGE"] == null ? 0 : decimal.Parse(row["N_DOSAGE"].ToString());
                entity.N_PRICE = row["N_PRICE"] == null ? 0 : decimal.Parse(row["N_PRICE"].ToString());

                entity.N_AMOUNT = row["N_AMOUNT"] == null ? 0 : decimal.Parse(row["N_AMOUNT"].ToString());
                entity.C_BANK = "";

                entity.C_REMARK = "";

                entitys.Add(entity);
            }

            if (entitys.Count > 0)
            {
                UpdateOrCreateRangeMonth1(applyId, proId, 2, entitys);
            }
        }

        /// <summary>
        /// 将所有部门详情GROUP合并并添加到汇总里面(周)
        /// </summary>
        public void GetWeekCollect(int applyId, int proId)
        {
            string sql = @"select N_PRO_ID,
       N_TYPE,
       N_PLANAPP_ID,
       C_PRONAME,
       round(sum(N_DOSAGE),6) N_DOSAGE,
       round(avg(N_PRICE),6) N_PRICE,
       round(sum(N_AMOUNT),6) N_AMOUNT,
       C_BANK,
       C_REMARK
  from qs_plan_detail1
 WHERE n_planapp_id = " + applyId + @"
   AND n_type = 5
   AND n_Pro_Id = " + proId + @"
 GROUP BY N_PRO_ID, N_TYPE, N_PLANAPP_ID, C_PRONAME,C_BANK,C_REMARK";

            var dt = Query(sql);
            var entitys = new List<QS_PLAN_DETAIL1>();
            decimal num = 0;
            foreach (System.Data.DataRow row in dt.Rows)
            {
                num++;

                var entity = new QS_PLAN_DETAIL1();

                entity.N_PRO_ID = decimal.Parse(row["N_PRO_ID"].ToString());
                entity.N_TYPE = 3;

                entity.N_PLANAPP_ID = decimal.Parse(row["N_PLANAPP_ID"].ToString());
                entity.N_NUM = num;
                entity.C_PRONAME = row["C_PRONAME"].ToString();

                entity.N_DOSAGE = row["N_DOSAGE"] == null ? 0 : decimal.Parse(row["N_DOSAGE"].ToString());
                entity.N_PRICE = row["N_PRICE"] == null ? 0 : decimal.Parse(row["N_PRICE"].ToString());

                entity.N_AMOUNT = row["N_AMOUNT"] == null ? 0 : decimal.Parse(row["N_AMOUNT"].ToString());
                entity.C_BANK = row["C_BANK"].ToString();

                entity.C_REMARK = row["C_REMARK"].ToString();

                entitys.Add(entity);
            }

            if (entitys.Count > 0)
            {
                UpdateOrCreateRangeMonth1(applyId, proId, 3, entitys);
            }
        }

        /// <summary>
        /// 查询周银行流水
        /// </summary>
        /// <param name="applyId"></param>
        /// <param name=""></param>

        #endregion 月资金相关

        public DataTable GetTable(decimal planappId, decimal type)
        {
            var sql = @"SELECT
                            ROW_NUMBER() OVER(ORDER BY T1.N_PRO_ID) as R,
	                        T1.*,
	                        CASE
		                        WHEN T1.N_AMOUNT=0 THEN '0%'
		                        WHEN T1.N_AMOUNT IS NULL THEN '0%'
		                        WHEN T1.N_USEAMOUNT=0 THEN '0%'
		                        WHEN T1.N_USEAMOUNT IS NULL THEN '0%'
		                        ELSE TO_CHAR(ROUND(T1.N_USEAMOUNT/T1.N_AMOUNT*100,2))||'%'
	                        END AS N_AMOUNTPROBABILITY
                        FROM QS_PLAN_DETAIL1 T1
                        WHERE
	                        T1.N_PLANAPP_ID = :APPLY_ID AND
	                        T1.N_TYPE = 3";
            var parms = new List<Tuple<string, string, string>>();
            parms.Add(":APPLY_ID", planappId, "int");
            return Query(sql, parms);
            /*
            var sql1 = string.Format(@"SELECT
                                            ROW_NUMBER() OVER(ORDER BY n_id1) as r,
                                            T2.N_ID1,
                                            T2.N_PRO_ID,
                                            T2.N_TYPE,
                                            T2.N_PLANAPP_ID,
                                            T2.N_NUM,
                                            T2.C_PRONAME,
                                            T2.N_DOSAGE,
                                            T2.N_DOSAGE,
                                            T1.C_PRONAME AS PRONAME
                                     FROM QS_PROJECT T1
                                     INNER JOIN QS_PLAN_DETAIL1 T2 ON T1.N_PROID = T2.N_PRO_ID
                                     WHERE T2.N_PLANAPP_ID = {0}
                                       AND T2.N_TYPE={1}
                                     ORDER BY
                                           T2.N_PRO_ID,
                                           T2.N_NUM", planappId, type);
            sum = 0;*/
            //DataTable dt = new BaseBLL().Query(sql1);
            /*********递归排序**********/
            /*
            var dt1 = dt.Clone();

            decimal PRO_R = 1;
            foreach (var item in dt.AsEnumerable().GroupBy(a => new { PRO_ID = Convert.ToInt32(a["N_PRO_ID"]), PRO_NAME = a["PRONAME"].ToString() }))
            {
                var entity = dt1.NewRow();
                entity["R"] = PRO_R;
                entity["n_amount"] = 0;
                entity["c_proname"] = item.Key.PRO_NAME;
                entity["n_pro_id"] = item.Key.PRO_ID;
                entity["N_AMOUNT"] = 0;
                entity["N_USEAMOUNT"] = 0;
                entity["C_BANK"] = "";
                entity["C_REMARK"] = "";
                dt1.Rows.Add(entity);
                foreach (var row in item)
                {
                    PRO_R = PRO_R + 1;
                    var entity1 = dt1.NewRow();
                    entity1["R"] = PRO_R;
                    entity1["n_id1"] = row["n_id1"];
                    entity1["n_pro_id"] = row["n_pro_id"];
                    entity1["n_type"] = row["n_type"];
                    entity1["n_planapp_id"] = row["n_planapp_id"];
                    entity1["n_num"] = row["n_num"];
                    entity1["c_proname"] = row["c_proname"];
                    entity1["n_dosage"] = row["n_dosage"];
                    entity1["n_price"] = row["n_price"];
                    entity1["n_amount"] = row["n_amount"];
                    entity1["c_bank"] = row["c_bank"];
                    entity1["c_remark"] = row["c_remark"];
                    entity1["PRONAME"] = row["PRONAME"];
                    entity["n_amount"] = Convert.ToDecimal(entity["n_amount"]) + Convert.ToDecimal(row["n_amount"]);
                    entity1["N_USEAMOUNT"] = row["N_USEAMOUNT"];
                    dt1.Rows.Add(entity1);
                }
                PRO_R = PRO_R + 1;
                sum += Convert.ToDecimal(entity["n_amount"]);
            }
            return dt1;
            */
        }

        /// <summary>
        /// 获取月资金主营业收入
        /// </summary>
        /// <param name="applyId"></param>
        /// <returns></returns>
        public DataTable GetTable1(int applyId)
        {
            var sql = @"SELECT
                          ROW_NUMBER() OVER(ORDER BY T1.C_PRONAME,T2.C_PRONAME) as R,
                          CASE
                             WHEN T1.C_PRONAME IS NULL THEN T2.C_PRONAME
                             ELSE T1.C_PRONAME
                          END AS C_PRONAME,
                          T2.N_DOSAGE,
                          T2.N_PRICE,
                          T2.N_AMOUNT AS N_WEEK_AMOUNT,
                          T2.N_USEAMOUNT AS N_WEEK_USEAMOUNT,
                          T2.N_GOL,
                          T2.C_REMARK,
                          T2.N_ID1,
                          T2.N_AMOUNT,
                          T2.N_USEAMOUNT,T2.N_PRO_ID,T2.N_PLANAPP_ID
                        FROM
                          (SELECT
                            T2.*
                          FROM QS_PLANMONTHAPPLY T1
                          INNER JOIN QS_PLAN_DETAIL1 T2 ON T1.N_MONTHAPP_ID = T2.N_PLANAPP_ID AND T2.N_TYPE = 2
                          WHERE
                            T1.N_MONTHAPP_ID = :MONTHAPP_ID
                          ) T1
                        FULL JOIN (
                        SELECT
                          T1.*,
                          CASE
                              WHEN T1.N_AMOUNT IS NULL THEN '0%'
                              WHEN T1.N_AMOUNT = 0 THEN '0%'
                              WHEN T1.N_USEAMOUNT IS NULL THEN '0%'
                              WHEN T1.N_USEAMOUNT = 0 THEN '0%'
                              ELSE TO_CHAR(ROUND(T1.N_USEAMOUNT/T1.N_AMOUNT*100,2))||'%'
                           END AS N_GOL
                        FROM
                        (
                          SELECT
                            T2.C_PRONAME,
                            SUM(
                              CASE
                                WHEN T2.N_DOSAGE IS NULL THEN 0
                                ELSE T2.N_DOSAGE
                              END) AS N_DOSAGE,
                            SUM(
                              CASE
                                WHEN T2.N_PRICE IS NULL THEN 0
                                ELSE T2.N_PRICE
                              END) AS N_PRICE,
                            SUM(
                              CASE
                                WHEN T2.N_AMOUNT IS NULL THEN 0
                                ELSE T2.N_AMOUNT
                              END) AS N_AMOUNT,
                            SUM(
                              CASE
                                WHEN T2.N_USEAMOUNT IS NULL THEN 0
                                ELSE T2.N_USEAMOUNT
                              END) AS N_USEAMOUNT,T2.N_PRO_ID,T2.N_PLANAPP_ID,T2.N_ID1,T2.C_REMARK
                          FROM QS_PLANWEEKAPPLY T1
                          INNER JOIN QS_PLAN_DETAIL1 T2 ON T1.N_WEEKAPP_ID = T2.N_PLANAPP_ID AND T2.N_TYPE = 3
                          INNER JOIN QS_PLANMONTHAPPLY T3 ON T1.N_MONTHAPP_ID = T3.N_MONTHAPP_ID
                          WHERE
                            T1.N_BUSINESS_STATE = 2 AND
                            T1.N_MONTHAPP_ID = :MONTHAPP_ID
                          GROUP BY
                            T2.C_PRONAME,T2.N_PRO_ID,T2.N_PLANAPP_ID,T2.N_ID1,T2.C_REMARK) T1) T2 ON T1.C_PRONAME = T2.C_PRONAME";
            var parms = new List<Tuple<string, string, string>>();
            parms.Add(":MONTHAPP_ID", applyId, "int");
            var dt = Query(sql, parms);
            return dt;
        }

        /// <summary>
        /// 获取年资金主营业收入
        /// </summary>
        /// <param name="applyId"></param>
        /// <returns></returns>
        public DataTable GetTable2(int applyId)
        {
            var sql = @"SELECT
   ROW_NUMBER() OVER(ORDER BY T1.C_PRONAME,T2.C_PRONAME) as R,
  CASE
     WHEN T1.C_PRONAME IS NULL THEN T2.C_PRONAME
     ELSE T1.C_PRONAME
  END AS C_PRONAME,
  T2.N_DOSAGE,
  T2.N_PRICE,
  T2.N_AMOUNT AS N_WEEK_AMOUNT,
  T2.N_USEAMOUNT AS N_WEEK_USEAMOUNT,
  T2.N_GOL,
  T2.C_REMARK,
  T2.N_ID1,
  T2.N_AMOUNT,
  T2.N_USEAMOUNT,T2.N_PRO_ID,T2.N_PLANAPP_ID
FROM
  (SELECT
    T1.*
  FROM QS_PLAN_DETAIL1 T1
  WHERE
    T1.N_TYPE = 1 AND
    T1.N_PLANAPP_ID = :APPLY_ID
  ) T1
FULL JOIN (
SELECT
  T1.*,
  CASE
      WHEN T1.N_AMOUNT IS NULL THEN '0%'
      WHEN T1.N_AMOUNT = 0 THEN '0%'
      WHEN T1.N_USEAMOUNT IS NULL THEN '0%'
      WHEN T1.N_USEAMOUNT = 0 THEN '0%'
      ELSE TO_CHAR(ROUND(T1.N_USEAMOUNT/T1.N_AMOUNT*100,2))||'%'
   END AS N_GOL
FROM
(
  SELECT
    T2.C_PRONAME,
    SUM(
      CASE
        WHEN T2.N_DOSAGE IS NULL THEN 0
        ELSE T2.N_DOSAGE
      END) AS N_DOSAGE,
    SUM(
      CASE
        WHEN T2.N_PRICE IS NULL THEN 0
        ELSE T2.N_PRICE
      END) AS N_PRICE,
    SUM(
      CASE
        WHEN T2.N_AMOUNT IS NULL THEN 0
        ELSE T2.N_AMOUNT
      END) AS N_AMOUNT,
    SUM(
      CASE
        WHEN T2.N_USEAMOUNT IS NULL THEN 0
        ELSE T2.N_USEAMOUNT
      END) AS N_USEAMOUNT,T2.N_ID1,T2.N_PRO_ID,T2.N_PLANAPP_ID,T2.C_REMARK
  FROM QS_PLANWEEKAPPLY T1
  INNER JOIN QS_PLAN_DETAIL1 T2 ON T1.N_WEEKAPP_ID = T2.N_PLANAPP_ID AND T2.N_TYPE = 3
  INNER JOIN QS_PLANMONTHAPPLY T3 ON T1.N_MONTHAPP_ID = T3.N_MONTHAPP_ID
  INNER JOIN QX_PLANYEARAPPLY T4 ON T3.N_YEARAPP_ID = T4.N_YEARAPP_ID
  WHERE
    T1.N_BUSINESS_STATE = 2 AND
    T4.N_BUSINESS_STATE = 2 AND
    T4.N_BUSINESS_STATE = 2 AND
    T4.N_YEARAPP_ID = :APPLY_ID
  GROUP BY
    T2.C_PRONAME,T2.N_ID1,T2.N_PRO_ID,T2.N_PLANAPP_ID,T2.C_REMARK) T1) T2 ON T1.C_PRONAME = T2.C_PRONAME";
            var parms = new List<Tuple<string, string, string>>();
            parms.Add(":APPLY_ID", applyId, "int");
            var dt = Query(sql, parms);
            return dt;
        }
    }
}